# -*- coding:utf-8 -*-
from __future__ import absolute_import
from celery import task,Celery,platforms
from django_web.models import SJB_ZY,SJX_ZY,TRANSFER_LOG

import os,datetime,time,xlrd
import cx_Oracle
platforms.C_FORCE_ROOT=True
os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'
@task
def transfer(file_name,ssml_sjb_bh):#异步任务
    is_create=SJB_ZY.objects.get(ssml_sjb_bh=ssml_sjb_bh).is_create
    data_sheet_name=SJB_ZY.objects.get(ssml_sjb_bh=ssml_sjb_bh).sjb_mc
    if is_create==0:
        creat_table(data_sheet_name,ssml_sjb_bh)
        transfer_into(file_name,data_sheet_name,ssml_sjb_bh)
    else:
        transfer_into(file_name,data_sheet_name,ssml_sjb_bh)

def  transfer_into(file_name,data_sheet_name,ssml_sjb_bh):#执行数据转换
    query = sql_insert_into(data_sheet_name, ssml_sjb_bh)
    sql_insert_into(data_sheet_name, ssml_sjb_bh)
    inputfile1 = os.path.abspath('.') + '/upload_excel/' + file_name
    data = get_excel(inputfile1)
    if data==[]:
        pass
    t_start = TRANSFER_LOG.objects.get(uplode_file_name=file_name)
    t_start.transfer_start_datetime=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    t_start.transfer_len= len(data)
    t_start.save()
    connection = cx_Oracle.connect('data/data123@127.0.0.1/helowin')
    curs = connection.cursor()
    if len(data)<1001:
        for i in data:
            i.append(file_name)
            curs.execute(query % tuple(i))
            print(i)
        connection.commit()
    else:
        counts = len(data)
        for i in data:
            i.append(file_name)
            curs.execute(query % tuple(i))
            print(i)
            a=counts%1000
            if a==0:
                print('tj')
                connection.commit()
    connection.commit()
    curs.close()
    connection.close()
    t_end = TRANSFER_LOG.objects.get(uplode_file_name=file_name)
    t_end.transfer_end_datetime=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    t_end.save()


def creat_table(sheet_name,ssml_sjb_bh):#在oracle数据库中创建对应表
    creat='CREATE TABLE'
    hecheng=creat+' '+sheet_name+'('
    sjx_mc = SJX_ZY.objects.filter(ssml_sjb_bh=ssml_sjb_bh)
    num=0
    douhao=len(sjx_mc)
    for item in sjx_mc:
        sjx=str(item.sjx_mc)
        num=num+1
        if num ==douhao:
            hecheng=hecheng+' '+ sjx +' varchar2(255),UPLOAD_FILE_NAME varchar2(255) )'
        else:
            hecheng=hecheng +  sjx +' varchar2(255),'
    connection = cx_Oracle.connect('data/data123@127.0.0.1/helowin')
    curs = connection.cursor()
    rr = curs.execute(hecheng)
    curs.close()
    connection.close()
    sjb_zy = SJB_ZY.objects.get(ssml_sjb_bh=ssml_sjb_bh)
    sjb_zy.is_create=1
    sjb_zy.save()

def sql_insert_into(sheet_name,ssml_sjb_bh):#执行sql语句插入数据
    sql_inser_into='INSERT INTO'
    sql_hecheng=sql_inser_into+' '+sheet_name+'('
    sjx_mc = SJX_ZY.objects.filter(ssml_sjb_bh=ssml_sjb_bh)
    num1=0
    douhao=len(sjx_mc)
    for item1 in sjx_mc:
        sjx=item1.sjx_mc
        num1=num1+1
        if num1 ==douhao:
            sql_hecheng=sql_hecheng+' '+ sjx +',UPLOAD_FILE_NAME)'
        else:
            sql_hecheng=sql_hecheng +  sjx +','
    sql_values = 'VALUES ('
    num2 = 0
    for item2 in range(douhao):
        num2 = num2 + 1
        if num2 ==douhao:
            sql_values = sql_values + '\'%s\'' +',\'%s\')'
        else:
            sql_values = sql_values + '\'%s\'' +','
    sql_all= sql_hecheng+sql_values
    return  sql_all

def get_excel(_filename):#获取excel数据
    data=[]
    workbook = xlrd.open_workbook(_filename)
    sheet1 = workbook.sheet_by_index(0)
    for i in range(1,sheet1.nrows):
        row = sheet1.row_values(i)
        data.append(row)
    return data